import sqlite3

db = sqlite3.connect("db.sql")

# Create table agent
db.execute("CREATE TABLE agent(id integer primary key autoincrement, username text)")
db.execute("CREATE INDEX idx_1 ON agent(username)")

# Create table crawl
db.execute("CREATE TABLE crawl(url text primary key)")

# Create table post
# Depth --> indicates what how many parent comments current comment has
# Submission --> the original post
# If the variables are not leaded by a prefix with underscore, it means that the variable is about
# the post itself
db.execute("CREATE TABLE post(id integer primary key autoincrement, submission_reddit_id text, depth integer, submission_timestamp int, timestamp int, submission_url text, external_url text, content text, score real, num_children integer, controversiality real)")
db.execute("CREATE INDEX idx_2 ON post(timestamp)")
db.execute("CREATE INDEX idx_3 ON post(submission_reddit_id)")